﻿using SqlSugar;
using System.Data;
using System.Diagnostics;

namespace WebAPI.Data
{
    public class SqlSugarHelper
    {
        //SqlSugarScope 线程安全 继承 静态
        //读写分离 单表记录500W以上
        //1.如果存在事务所有操作都走主库，不存在事务 修改、写入、删除走主库，查询操作走从库
        //2.HitRate 越大走这个从库的概率越大
        public static SqlSugarScope Db = new SqlSugarScope(new ConnectionConfig()
        {
            ConnectionString = "server = DESKTOP-UVNILR1\\SQLEXPRESS; Database = csharp; Uid = sa; Pwd = sa@123;",//主库连接符字串
            DbType = SqlSugar.DbType.SqlServer,//数据库类型
            IsAutoCloseConnection = true, //不设成true要手动close

            //从库
            SlaveConnectionConfigs = new List<SlaveConnectionConfig>() {
                new SlaveConnectionConfig() { HitRate=10, ConnectionString="server = DESKTOP-UVNILR1\\SQLEXPRESS; Database = csharp; Uid = sa; Pwd = sa@123;" }
            }
        },
        db =>
        {
            //调试SQL事件
            db.Aop.OnLogExecuting = (sql, pars) =>
            {
                //获取原生SQL推荐 5.1.4.63  性能OK
                var sqlStr = UtilMethods.GetNativeSql(sql, pars);
                Debug.WriteLine(sqlStr);

                //获取无参数化SQL 影响性能只适合调试
                //UtilMethods.GetSqlString(DbType.SqlServer,sql,pars)
            };
            db.Aop.OnLogExecuted = (sql, pars) =>
            {
                //执行时间超过1秒
                if (db.Ado.SqlExecutionTime.TotalSeconds > 1)
                {
                    //代码CS文件名
                    var fileName = db.Ado.SqlStackTrace.FirstFileName;
                    //代码行数
                    var fileLine = db.Ado.SqlStackTrace.FirstLine;
                    //方法名
                    var FirstMethodName = db.Ado.SqlStackTrace.FirstMethodName;
                    //sql
                    var exesql = sql;
                    //参数
                    var sqlpars = pars;
                    //db.Ado.SqlStackTrace.MyStackTraceList[1].xxx 获取上层方法的信息
                }
            };
            //SQL报错
            db.Aop.OnError = (exp) =>
            {
                Console.WriteLine(exp.Sql);
                //exp.sql 这样可以拿到错误SQL            
            };
            db.Aop.OnExecutingChangeSql = (sql, pars) => //可以修改SQL和参数的值
            {
                //sql=newsql
                //foreach(var p in pars) //修改
                return new KeyValuePair<string, SugarParameter[]>(sql, pars);
            };
        });

        // 执行存储过程
        public static DataTable UseStoredProcedure(string procedureName, List<SugarParameter> parameters)
        {
            var result = Db.Ado.UseStoredProcedure().GetDataTable(procedureName, parameters);
            return result;
        }

        public static (DataTable, List<SugarParameter>) UseStoredProcedureToTuple(string procedureName, List<SugarParameter> parameters)
        {
            var result = (Db.Ado.UseStoredProcedure().GetDataTable(procedureName, parameters), parameters);
            return result;
        }

        // 执行存储过程
        public static void ExecuteProcedure(string sprocName, Dictionary<string, object> inParams, Dictionary<string, object> outParams)
        {
            List<SugarParameter> pList = new List<SugarParameter>();
            if (inParams != null)
            {
                pList = inParams.Select(obj => new SugarParameter($@"@{obj.Key}", obj.Value)).ToList();
            }
            //if (outParams != null)
            //{
            //    pList.AddRange(outParams.Select(obj => new SugarParameter($@"@{obj.Key}", obj.Value)
            //    {
            //        Direction = ParameterDirection.Output
            //    }));
            //}
            Db.Ado.UseStoredProcedure().ExecuteCommand(sprocName, pList);
            foreach (var p in pList.Where(r => r.Direction == ParameterDirection.Output))
            {
                var pName = p.ParameterName.Substring(1);
                if (outParams != null && outParams.ContainsKey(pName))
                {
                    outParams[pName] = p.Value;
                }
            }
        }

        public static void Demo()
        {
            //设置超时时间
            //方法一：在连接字符串中添加 Connect Timeout=30；
            //方法二：创建对象中添加代码 db.Ado.CommandTimeOut = 30;//单位秒

            //验证连接是否成功
            //bool connect = db.Ado.IsValidConnection();//true：代表连接成功

            //使用长连接
            //using (db.Ado.OpenAlways())
            //{
            //     db.Queryable...
            //     db.Insertable...
            //}
        }
    }
}